﻿using System.Collections.Generic;
using HCQ2_IDAL;
using HCQ2_Model.APPModel.ResultApiModel;
using HCQ2_Model.APPModel.ParamModel;
using System.Data;
using System;
using System.Globalization;
using System.Text;
using HCQ2_Common.SQL;
using HCQ2_Common;
using HCQ2_Common.Data;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_ClassDAL: IT_ClassDAL
    {
        //******************************************接口*******************************************
        /// <summary>
        ///  获取班级列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ClassListResult> GetClassList(ClassListModel param,int userId)
        {
            #region 根据用户所在班级推学校：注释
            //      sb?.Clear();
            //      sb.AppendFormat(@"SELECT * FROM (SELECT s.id,s.className,s.entranceYear,s.classStudentNum,s.teacherName,s.remark,
            //       [userPhone]=(SELECT user_phone FROM dbo.T_User WHERE user_id=s.teacherUserId),
            //       [mark]=ISNULL((SELECT CAST(mark AS INT) FROM T_VRFocusClass WHERE user_id={0} AND class_id=s.id),0) FROM dbo.T_Class s WHERE  s.id IN( SELECT tClass.id FROM 
            //(SELECT studentId FROM dbo.T_User WHERE user_id={0})users INNER JOIN
            //(SELECT studentId,schoolName FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId INNER JOIN
            //(SELECT id,schoolName FROM dbo.T_Class)tClass ON tClass.schoolName = tStudent.schoolName))a ", userId);
            //      if (param.entranceYear > 0)
            //          sb.AppendFormat(" WHERE a.entranceYear={0} ", param.entranceYear);
            //      DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            //      return DataTableHelper.DataTableToIList<ClassListResult>(dt);
            #endregion
            sb?.Clear();
            sb.AppendFormat($@"SELECT * FROM (SELECT s.id,s.className,s.entranceYear,s.classStudentNum,s.teacherName,s.remark,
             [userPhone]=(SELECT user_phone FROM dbo.T_User WHERE user_id=s.teacherUserId),
             [mark]=ISNULL((SELECT CAST(mark AS INT) FROM T_VRFocusClass WHERE user_id={userId} AND class_id=s.id),0) FROM dbo.T_Class s WHERE  s.schoolCode IN(SELECT unit_code FROM dbo.T_User WHERE user_id={userId}) ");
            //已关注班级
            if (param.markStatus == 1)
                sb.Append($" AND s.id IN(SELECT class_id FROM dbo.T_VRFocusClass WHERE user_id={userId} AND mark=1) ");
            //未关注班级
            else if(param.markStatus == 0)
                sb.Append($" AND s.id NOT IN(SELECT class_id FROM dbo.T_VRFocusClass WHERE user_id={userId} AND mark=1) ");
            sb.Append(")a");
            if (param.entranceYear > 0)
                sb.AppendFormat($" WHERE a.entranceYear={param.entranceYear} ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return DataTableHelper.DataTableToIList<ClassListResult>(dt);
        }
        /// <summary>
        ///  获取关注班级列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ClassListResult> GetFocusClassList(ClassListModel model, int userId, int focus)
        {
            sb?.Clear();
            if (model.markStatus==0 || focus > 0)
            {
                //获取已关注的班级
                sb.Append($@"SELECT * FROM (SELECT s.id,s.className,s.entranceYear,s.classStudentNum,s.teacherName,s.remark,
                [userPhone]=(SELECT user_phone FROM dbo.T_User WHERE user_id=s.teacherUserId),[mark]=1
                FROM dbo.T_Class s WHERE s.id IN(SELECT class_id FROM T_VRFocusClass WHERE user_id={userId} AND mark=1))a ");
                if(model.entranceYear>0)
                    sb.Append($" WHERE a.entranceYear={model.entranceYear} ");
            }else
            {
                //用户所属单位下的全部班级
                sb.AppendFormat($@"SELECT * FROM (SELECT s.id,s.className,s.entranceYear,s.classStudentNum,s.teacherName,s.remark,
                 [userPhone]=(SELECT user_phone FROM dbo.T_User WHERE user_id=s.teacherUserId),
                 [mark]=ISNULL((SELECT CAST(mark AS INT) FROM T_VRFocusClass WHERE user_id={userId} AND class_id=s.id),0) FROM dbo.T_Class s WHERE  s.schoolCode IN(SELECT unit_code FROM dbo.T_User WHERE user_id={userId}))a ");
                if (model.entranceYear > 0)
                    sb.AppendFormat($" WHERE a.entranceYear={model.entranceYear} ");
            }
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return DataTableHelper.DataTableToIList<ClassListResult>(dt);
        }
        /// <summary>
        ///  获取体验时长排行榜
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<ExperienceRankList> GetExperienceRankList(ExperienceRankParam param,int userId)
        {
            sb.Clear();
            StringBuilder _sb = new StringBuilder();
            int mark = Helper.ToInt(SqlHelper.ExecuteScalar(string.Format("SELECT COUNT(0) AS num FROM T_VRFocusClass WHERE user_id={0};", userId)));
            if (param.classId > 0)
                _sb.AppendFormat(" s.classId={0} ", param.classId);
            else if (mark > 0)
                _sb.AppendFormat(" s.classId IN(SELECT class_id FROM T_VRFocusClass vr WHERE vr.user_id={0}) ", userId);
            else
                _sb.AppendFormat(@"s.classId IN(
				SELECT tClass.id FROM 
				(SELECT studentId FROM dbo.T_User WHERE user_id={0})users INNER JOIN
				(SELECT studentId,schoolName FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId INNER JOIN
				(SELECT id,schoolName FROM dbo.T_Class)tClass ON tClass.schoolName = tStudent.schoolName) ", userId);
            sb.AppendFormat(@"SELECT TOP {0} * FROM 
            (SELECT c.user_id,c.studentName AS user_name,c.schoolName,c.className,em_time=ISNULL((SELECT sum(CAST(ISNULL(em_time,0) AS INT)) from T_VRExperienceRecord WITH(nolock) WHERE user_id=c.user_id),0)
            FROM
            (
	            SELECT b.schoolName,b.user_id,b.studentName 
	            ,in_date=(SELECT in_date from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)
	            ,out_date=(SELECT out_date from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)
	            ,className=(SELECT className FROM dbo.T_Class WHERE id=b.classId) 
	             FROM 
	            (
		            SELECT *,er_id=(SELECT TOP 1 er_id FROM T_VRExperienceRecord WITH(nolock) WHERE user_id=a.user_id ORDER BY in_date desc)FROM 
		            (
			            SELECT s.classId,s.schoolName,s.studentName,[user_id]=(SELECT user_id FROM dbo.T_User WHERE studentId=s.studentId) FROM dbo.T_Student s WHERE ", param.size);
            sb.AppendFormat(_sb.ToString());
            sb.AppendFormat(@") a) b) c WHERE 1=1 ");
            //开始时间，结束时间
            if (!string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.in_date>='{0}' AND c.in_date<='{1}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.in_date) && string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.in_date>='{0}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.in_date<='{0}' ", DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb.AppendFormat(")d ORDER BY d.em_time DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return DataTableHelper.DataTableToIList<ExperienceRankList>(dt);
        }
        private string Execttime(string inDate,string outDate)
        {
            StringBuilder _sb = new StringBuilder();
            if (!string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                _sb.AppendFormat(" AND in_date>= '{0}' AND in_date<='{1}' ", DateTime.ParseExact(inDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(outDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(inDate) && string.IsNullOrEmpty(outDate))
                _sb.AppendFormat(" AND in_date>= '{0}' ", DateTime.ParseExact(inDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                _sb.AppendFormat(" AND in_date<= '{0}' ", DateTime.ParseExact(outDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            return _sb.ToString();
        }
        /// <summary>
        ///  获取体验正确率
        /// </summary>
        /// <param name="param"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<CorrectRateResult> GetCorrectRateList(ExperienceRankParam param, int userId)
        {
            sb.Clear();
            StringBuilder _sb = new StringBuilder();
            int mark = Helper.ToInt(SqlHelper.ExecuteScalar(string.Format("SELECT COUNT(0) AS num FROM T_VRFocusClass WHERE user_id={0};", userId)));
            if (param.classId > 0)
                _sb.AppendFormat(" s.classId={0} ", param.classId);
            else if (mark > 0)
                _sb.AppendFormat(" s.classId IN(SELECT class_id FROM T_VRFocusClass vr WHERE vr.user_id={0}) ", userId);
            else
                _sb.AppendFormat(@"s.classId IN(
				SELECT tClass.id FROM 
				(SELECT studentId FROM dbo.T_User WHERE user_id={0})users INNER JOIN
				(SELECT studentId,schoolName FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId INNER JOIN
				(SELECT id,schoolName FROM dbo.T_Class)tClass ON tClass.schoolName = tStudent.schoolName) ", userId);
            sb.AppendFormat(@"SELECT a.user_id,a.studentName AS user_name,a.schoolName,a.className,a.ed_status FROM 
            (SELECT su.user_id,su.studentName,su.schoolName,su.className,detail.ed_status,detail.in_date FROM 
            (SELECT s.classId,s.schoolName,s.studentName,
            [className]=(SELECT className FROM dbo.T_Class WHERE id=s.classId)
            ,[user_id]=(SELECT user_id FROM dbo.T_User WHERE studentId=s.studentId) FROM dbo.T_Student s WHERE");
            sb.AppendFormat(_sb.ToString());
            sb.AppendFormat(@")su LEFT JOIN
            (SELECT er_id,user_id FROM T_VRExperienceRecord)record ON su.user_id=record.user_id	INNER JOIN
            (SELECT er_id,word,ed_status,in_date FROM T_VRExperienceDetail)detail ON record.er_id=detail.er_id)a WHERE 1=1 ");
            //开始时间，结束时间
            if (!string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND a.in_date>='{0}' AND a.in_date<='{1}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.in_date) && string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND a.in_date>='{0}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND a.in_date<='{0}' ", DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            DataTable dt = SqlHelper.ExecuteDataTable(GetRateSql(sb.ToString(), param.size));
            return DataTableHelper.DataTableToIList<CorrectRateResult>(dt);
        }
        private string GetRateSql(string sql,int size)
        {
            StringBuilder _sb = new StringBuilder();
            _sb.AppendFormat("DECLARE @table TABLE(user_id int,user_name NVARCHAR(50),schoolName NVARCHAR(100),className NVARCHAR(100),ed_status BIT);");
            _sb.AppendFormat("INSERT INTO @table ");
            _sb.AppendFormat(sql);
            _sb.AppendFormat(@"SELECT TOP {0} record.user_name AS userName,record.className,record.schoolName,
                (CASE WHEN record.rateCount=0 OR record.rateOk=0 THEN 0 ELSE CONVERT(decimal(18,2),(CAST(record.rateOk AS FLOAT)/CAST(record.rateCount AS FLOAT))*100) END)AS accuracy,
                 record.rateCount,record.rateOk FROM 
                (SELECT DISTINCT a.user_id,a.user_name,a.schoolName,a.className,
                rateCount=(SELECT COUNT(0) FROM @table b WHERE a.user_id=b.user_id),
                rateOk=(SELECT COUNT(0) FROM @table c WHERE c.ed_status=1 AND a.user_id=c.user_id)
                 FROM @table a)record ", size);
            return _sb.ToString();
        }
        /// <summary>
        ///  获取单词出错次数统计
        /// </summary>
        /// <param name="param"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<WordErrorModel> GetWordErrorNumList(ExperienceRankParam param, int userId)
        {
            sb.Clear();
            StringBuilder _sb = new StringBuilder();
            int mark = Helper.ToInt(SqlHelper.ExecuteScalar(string.Format("SELECT COUNT(0) AS num FROM T_VRFocusClass WHERE user_id={0};", userId)));
            if (param.classId > 0)
                _sb.AppendFormat(" s.classId={0} ", param.classId);
            else if (mark > 0)
                _sb.AppendFormat(" s.classId IN(SELECT class_id FROM T_VRFocusClass vr WHERE vr.user_id={0}) ", userId);
            else
                _sb.AppendFormat(@"s.classId IN(
				SELECT tClass.id FROM 
				(SELECT studentId FROM dbo.T_User WHERE user_id={0})users INNER JOIN
				(SELECT studentId,schoolName FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId INNER JOIN
				(SELECT id,schoolName FROM dbo.T_Class)tClass ON tClass.schoolName = tStudent.schoolName) ", userId);
            sb.AppendFormat(@"SELECT a.word,a.ed_status FROM 
            (SELECT detail.word,detail.ed_status,detail.in_date FROM 
            (SELECT s.classId,s.schoolName,s.studentName,
            [className]=(SELECT className FROM dbo.T_Class WHERE id=s.classId)
            ,[user_id]=(SELECT user_id FROM dbo.T_User WHERE studentId=s.studentId) FROM dbo.T_Student s WHERE ");
            sb.AppendFormat(_sb.ToString());
            sb.AppendFormat(@")su LEFT JOIN
            (SELECT er_id,user_id FROM T_VRExperienceRecord)record ON su.user_id=record.user_id	INNER JOIN
            (SELECT er_id,word,ed_status,in_date FROM T_VRExperienceDetail WHERE ed_status=0)detail ON record.er_id=detail.er_id)a WHERE 1=1 ");
            //开始时间，结束时间
            if (!string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND a.in_date>='{0}' AND a.in_date<='{1}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.in_date) && string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND a.in_date>='{0}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND a.in_date<='{0}' ", DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            DataTable dt = SqlHelper.ExecuteDataTable(GetErrorRateSql(sb.ToString(), param.size));
            return DataTableHelper.DataTableToIList<WordErrorModel>(dt);
        }
        private string GetErrorRateSql(string sql, int size)
        {
            StringBuilder _sb = new StringBuilder();
            _sb.AppendFormat("DECLARE @table TABLE(word NVARCHAR(50),ed_status BIT);");
            _sb.AppendFormat("INSERT INTO @table ");
            _sb.AppendFormat(sql);
            _sb.AppendFormat(@"SELECT TOP {0} * FROM (SELECT DISTINCT word,errorNum=(SELECT COUNT(0) FROM @table a WHERE a.word=resord.word) FROM @table resord)tRecord ORDER BY tRecord.errorNum DESC;", size);
            return _sb.ToString();
        }
        /// <summary>
        ///  获取用户登录信息
        /// </summary>
        /// <param name="param"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<UserInfoByLoginModel> GetUserLoginInfo(ExperienceRankParam param, int userId)
        {
            #region 查询用户
            if (param.userType > 0)
            {
                string unitCode = (!string.IsNullOrEmpty(param.unit_code)) ? $" AND unit_code='{param.unit_code}' " : "";
                sb?.Clear();
                sb.Append($@"SELECT TOP {param.size} users.*,logins.loginTime,tClass.* FROM 
                (SELECT studentId,user_id,user_name AS userName,login_name AS loginName FROM dbo.T_User WHERE status=1 {unitCode} )users INNER JOIN
                (SELECT user_id,CONVERT(varchar(100),login_time,120) AS loginTime FROM dbo.T_Login WHERE 1=1 ");
                //开始时间，结束时间
                if (!string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                    sb.AppendFormat(" AND login_time>='{0}' AND login_time<='{1}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
                else if (!string.IsNullOrEmpty(param.in_date) && string.IsNullOrEmpty(param.out_date))
                    sb.AppendFormat(" AND login_time>='{0}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
                else if (string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                    sb.AppendFormat(" AND login_time<='{0}' ", DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
                sb.Append($@")logins ON logins.user_id = users.user_id LEFT JOIN
                (SELECT studentId, schoolName,classId FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId LEFT JOIN
                (SELECT className, schoolName,id FROM dbo.T_Class)tClass ON tClass.id = tStudent.classId ORDER BY logins.loginTime DESC; ");
                DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
                return DataTableHelper.DataTableToIList<UserInfoByLoginModel>(dt);
            }
            #endregion

            #region 查询学生

            else
            {
                sb.Clear();
                StringBuilder _sb = new StringBuilder();
                int mark = Helper.ToInt(SqlHelper.ExecuteScalar(string.Format("SELECT COUNT(0) AS num FROM T_VRFocusClass WHERE user_id={0};", userId)));
                if (param.classId > 0)
                    _sb.AppendFormat(" s.classId={0} ", param.classId);
                else if (mark > 0)
                    _sb.AppendFormat(" s.classId IN(SELECT class_id FROM T_VRFocusClass vr WHERE vr.user_id={0}) ", userId);
                else
                    _sb.AppendFormat(@"s.classId IN(
                SELECT tClass.id FROM 
                (SELECT studentId FROM dbo.T_User WHERE user_id={0})users INNER JOIN
                (SELECT studentId,schoolName FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId INNER JOIN
                (SELECT id,schoolName FROM dbo.T_Class)tClass ON tClass.schoolName = tStudent.schoolName) ", userId);
                sb.AppendFormat($@"SELECT TOP {param.size} c.userName,c.loginName,CONVERT(varchar(100),c.loginTime,120)as loginTime,c.schoolName,c.className
                        FROM
                        (
                         SELECT *
                         ,loginTime=(SELECT TOP 1 login_time FROM dbo.T_Login WITH(nolock) WHERE user_id=b.user_id ORDER BY login_time desc)
                          FROM 
                         (
                          SELECT a.studentName AS userName,a.loginName,a.schoolName,a.className,a.user_id FROM 
                          (
                           SELECT s.classId,s.studentName,s.studentId,[user_id]=(SELECT user_id FROM dbo.T_User WHERE studentId=s.studentId),
                           [className]=(SELECT className FROM dbo.T_Class WHERE id=s.classId),s.schoolName,
                           [loginName]=(SELECT login_name FROM dbo.T_User WHERE studentId=s.studentId) FROM dbo.T_Student s WHERE ");
                sb.AppendFormat(_sb.ToString());
                sb.AppendFormat(@") a) b) c WHERE ISNULL(c.loginTime,'')<>'' ");
                //开始时间，结束时间
                if (!string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                    sb.AppendFormat(" AND c.loginTime>='{0}' AND c.loginTime<='{1}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
                else if (!string.IsNullOrEmpty(param.in_date) && string.IsNullOrEmpty(param.out_date))
                    sb.AppendFormat(" AND c.loginTime>='{0}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
                else if (string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                    sb.AppendFormat(" AND c.loginTime<='{0}' ", DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
                DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
                return DataTableHelper.DataTableToIList<UserInfoByLoginModel>(dt);
            }

            #endregion
        }
        private string Logintime(string inDate, string outDate)
        {
            StringBuilder _sb = new StringBuilder();
            if (!string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                _sb.AppendFormat(" AND login_time>= '{0}' AND login_time<='{1}' ", DateTime.ParseExact(inDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(outDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(inDate) && string.IsNullOrEmpty(outDate))
                _sb.AppendFormat(" AND login_time>= '{0}' ", DateTime.ParseExact(inDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                _sb.AppendFormat(" AND login_time<= '{0}' ", DateTime.ParseExact(outDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            return _sb.ToString();
        }
        /// <summary>
        ///  获取学生列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<StudentInfoResult> GetStudentInfoList(StudentLoginModel param, int userId)
        {
            sb.Clear();
            StringBuilder _sb = new StringBuilder();
            int mark = Helper.ToInt(SqlHelper.ExecuteScalar(string.Format("SELECT COUNT(0) AS num FROM T_VRFocusClass WHERE user_id={0};", userId)));
            if (param.classId > 0)
                _sb.AppendFormat(" s.classId={0} ", param.classId);
            else if (mark > 0)
                _sb.AppendFormat(" s.classId IN(SELECT class_id FROM T_VRFocusClass vr WHERE vr.user_id={0}) ", userId);
            else
                #region 注释
    //            _sb.AppendFormat(@"s.classId IN(
				//SELECT tClass.id FROM 
				//(SELECT studentId FROM dbo.T_User WHERE user_id={0})users INNER JOIN
				//(SELECT studentId,schoolName FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId INNER JOIN
				//(SELECT id,schoolName FROM dbo.T_Class)tClass ON tClass.schoolName = tStudent.schoolName) ", userId);
            #endregion
            _sb.AppendFormat($@"s.classId IN(
				SELECT tClass.id FROM 
				(SELECT unit_code,user_unit FROM dbo.T_User WHERE user_id={userId})users INNER JOIN
				(SELECT id,schoolCode FROM dbo.T_Class)tClass ON users.unit_code=tClass.schoolCode) ");
            sb.AppendFormat(@"SELECT record1.*,accuracy=0,main.result_ok,main.result_error,ISNULL(star.star_num,0)AS star_num FROM (SELECT TOP {0} c.studentName AS user_name,c.user_img,c.user_id,c.class_name,c.folder_name AS mark_type,c.file_name,c.file_id,c.guanka AS folder_name,CONVERT(varchar(100),c.in_date,120) AS in_date,
            CONVERT(varchar(100),c.out_date,120) AS out_date,CONVERT(varchar(100),c.login_time,120)AS login_time,ISNULL(c.line_status,'离线') as line_status
            ,em_time=(SELECT sum(CAST(ISNULL(em_time,0) AS INT)) from T_VRExperienceRecord WITH(nolock) WHERE user_id=c.user_id AND folder_name=c.guanka AND file_id=c.file_id)--体验时长
            ,c.em_times,c.em_score,user_guid=(SELECT user_guid FROM dbo.T_User WHERE user_id=c.user_id)
            FROM
            (
	            SELECT *
	            ,class_name=(SELECT className FROM dbo.T_Class WHERE id=b.classId)
	            ,folder_name=(SELECT folder_name FROM dbo.T_DocumentFolder WITH(nolock) WHERE folder_id=(SELECT folder_id FROM dbo.T_DocumentFolderRelation WITH(nolock) WHERE file_id=b.file_id))--课件分类
	            ,file_name=(SELECT file_name from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)--课件名称
	            ,guanka=(SELECT folder_name from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)--关卡
	            ,in_date=(SELECT in_date from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)--进入时间
	            ,out_date=(SELECT out_date from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)--离开时间
	            ,login_time=(SELECT TOP 1 login_time FROM dbo.T_Login WITH(nolock) WHERE user_id=b.user_id ORDER BY login_time desc)--登录时间
	            ,line_status=ISNULL((SELECT TOP 1 line_status FROM dbo.T_VROnLineUser WITH(nolock) WHERE user_id=b.user_id ORDER BY on_id desc),'离线')--状态
	             FROM 
	            (
		            SELECT *,er_id=(SELECT TOP 1 er_id FROM T_VRExperienceRecord WITH(nolock) WHERE user_id=a.user_id ORDER BY in_date desc)
                    ,em_times=(SELECT sum(CAST(ISNULL(em_time,0) AS INT)) from T_VRExperienceRecord WITH(nolock) WHERE user_id=a.user_id)
                    ,em_score=(SELECT SUM(ISNULL(score,0)) FROM T_VRExperienceRecord WITH(nolock) WHERE user_id=a.user_id)
		            ,[file_id]=(SELECT TOP 1 [file_id] FROM T_VRExperienceRecord WITH(nolock) WHERE user_id=a.user_id ORDER BY in_date desc) FROM 
		            (
			            SELECT s.classId,s.studentName,s.studentId,[user_img]=(SELECT user_img FROM dbo.T_User WHERE studentId=s.studentId),[user_id]=(SELECT user_id FROM dbo.T_User WHERE studentId=s.studentId) FROM dbo.T_Student s WHERE ", param.size);
            sb.AppendFormat(_sb.ToString());
            sb.AppendFormat(@") a) b) c WHERE 1=1 ");
            //课件名称
            if (!string.IsNullOrEmpty(param.fileName))
                sb.AppendFormat(" AND c.file_name='{0}' ", param.fileName);
            //关卡名称
            if (!string.IsNullOrEmpty(param.folderName))
                sb.AppendFormat(" AND c.guanka='{0}' ", param.folderName);
            //开始时间，结束时间
            if (!string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.login_time>='{0}' AND c.login_time<='{1}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if(!string.IsNullOrEmpty(param.in_date) && string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.login_time>='{0}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if(string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.login_time<='{0}' ", DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            //状态
            if (param.line_status == 1)
                sb.AppendFormat(" AND c.line_status='在线' ");
            else if (param.line_status == 0)
                sb.AppendFormat(" AND c.line_status='离线' ");
            sb.Append(" )record1 LEFT JOIN(SELECT * FROM dbo.T_VRExperienceRecordMain)main ON record1.user_id=main.user_id AND record1.file_id=main.file_id AND record1.folder_name=main.folder_name  LEFT JOIN (SELECT COUNT(0)AS star_num,user_guid FROM dbo.T_VRUserFolder GROUP BY user_guid)star ON star.user_guid = record1.user_guid;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return DataTableHelper.DataTableToIList<StudentInfoResult>(dt);
        }
        /// <summary>
        ///  获取在线体验用户数量
        /// </summary>
        /// <param name="param"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int GetOnLineNum(StudentLoginModel param, int userId)
        {
            sb.Clear();
            StringBuilder _sb = new StringBuilder();
            int mark = Helper.ToInt(SqlHelper.ExecuteScalar(string.Format("SELECT COUNT(0) AS num FROM T_VRFocusClass WHERE user_id={0};", userId)));
            if (param.classId > 0)
                _sb.AppendFormat(" s.classId={0} ", param.classId);
            else if (mark > 0)
                _sb.AppendFormat(" s.classId IN(SELECT class_id FROM T_VRFocusClass vr WHERE vr.user_id={0}) ", userId);
            else
                _sb.AppendFormat(@"s.classId IN(
				SELECT tClass.id FROM 
				(SELECT studentId FROM dbo.T_User WHERE user_id={0})users INNER JOIN
				(SELECT studentId,schoolName FROM dbo.T_Student)tStudent ON tStudent.studentId = users.studentId INNER JOIN
				(SELECT id,schoolName FROM dbo.T_Class)tClass ON tClass.schoolName = tStudent.schoolName) ", userId);
            sb.AppendFormat(@"SELECT COUNT(0) AS num
                FROM
                (
	                SELECT b.user_id
	                ,in_date=(SELECT in_date from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)--进入时间
	                ,out_date=(SELECT out_date from T_VRExperienceRecord WITH(nolock) WHERE er_id=b.er_id)--离开时间
	                ,line_status=(SELECT TOP 1 line_status FROM dbo.T_VROnLineUser WITH(nolock) WHERE user_id=b.user_id ORDER BY on_id desc)--状态
	                 FROM 
	                (
		                SELECT a.user_id,er_id=(SELECT TOP 1 er_id FROM T_VRExperienceRecord WITH(nolock) WHERE user_id=a.user_id ORDER BY in_date desc)FROM 
		                (
			                SELECT [user_id]=(SELECT user_id FROM dbo.T_User WHERE studentId=s.studentId) FROM dbo.T_Student s WHERE");
            sb.AppendFormat(_sb.ToString());
            sb.AppendFormat(@") a) b) c WHERE c.line_status='在线' ");
            //开始时间，结束时间
            if (!string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.in_date>='{0}' AND c.in_date<='{1}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.in_date) && string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.in_date>='{0}' ", DateTime.ParseExact(param.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.in_date) && !string.IsNullOrEmpty(param.out_date))
                sb.AppendFormat(" AND c.in_date<='{0}' ", DateTime.ParseExact(param.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}
